﻿// Copyright 2012 T4C: sejerpz@gmail.com
// Licensed under GPL v3

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;
using SET.Core;

namespace SET.Statistics
{
    class Database
    {
        private IDbConnection connection;

        public Database(string databasePath)
        {
            try
            {
                bool existsDb = File.Exists(databasePath);
                connection = new SQLiteConnection(string.Format("URI=file:{0}", databasePath));
                connection.Open();
                if (!existsDb)
                {

                    CreateSchema();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private void CreateSchema()
        {
            try
            {
                string sql = "CREATE TABLE Stats (Id INTEGER PRIMARY KEY, Date DATETIME, Exercise SMALLINT, ExercisePlayed VARCHAR(50), CorrectAnswer VARCHAR(20), UserAnswer VARCHAR(20));";
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public void InsertStat(Excercise excercise, string exercisePlayed, string correctAnswer, string userAnswer)
        {
            try
            {
                string sql = "INSERT INTO Stats (Id, Date, Exercise, ExercisePlayed, CorrectAnswer, UserAnswer) VALUES (?, ?, ?, ?, ?, ?);";
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;

                    var par = command.CreateParameter();
                    par.Value = DBNull.Value;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = DateTime.Now;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = (short)excercise;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = exercisePlayed;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = correctAnswer;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = userAnswer;
                    command.Parameters.Add(par);

                    command.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        internal DataTable GetSummaryData(Excercise excercise, DateTime from, DateTime to)
        {
            try
            {
                DataTable results = new DataTable();
                string sql = "SELECT SUM(CASE WHEN CorrectAnswer <> UserAnswer THEN 0 ELSE 1 END) as CorrectCount, COUNT(*) AS TotalCount FROM Stats WHERE Exercise = ? AND Date >= ? AND Date <= ?";

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;

                    var par = command.CreateParameter();
                    par.Value = (short)excercise;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = from;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = to;
                    command.Parameters.Add(par);

                    var adapter = new SQLiteDataAdapter((SQLiteCommand)command);
                    adapter.Fill(results);
                }
                return results;
            }
            catch (Exception)
            {

                throw;
            }
           
        }

        internal DataTable GetData(Excercise excercise, DateTime from, DateTime to)
        {
            try
            {
                DataTable results = new DataTable();
                string sql = "SELECT *, CASE WHEN CorrectAnswer <> UserAnswer THEN 0 ELSE 1 END as Correct FROM Stats WHERE Exercise = ? AND Date >= ? AND Date <= ? ORDER BY Date DESC";

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;

                    var par = command.CreateParameter();
                    par.Value = (short)excercise;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = from;
                    command.Parameters.Add(par);

                    par = command.CreateParameter();
                    par.Value = to;
                    command.Parameters.Add(par);

                    var adapter = new SQLiteDataAdapter((SQLiteCommand)command);
                    adapter.Fill(results);
                }
                return results;
            }
            catch (Exception)
            {

                throw;
            }

        }
    }
}
